package com.nondo.dean.saprfc.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public final class DBUtil extends BaseBean {
	private static final Map<String, ComboPooledDataSource> map = new HashMap<String, ComboPooledDataSource>();
	public static final String OA ="loc";
	private DBUtil() {
	}

	private static ComboPooledDataSource getDataSource(String propName)
			throws Exception {
		ComboPooledDataSource dataSource = null;
		if (map.containsKey(propName)) {
			dataSource = map.get(propName);
		} else {
			String fileName = propName + ".properties";
			dataSource = new ComboPooledDataSource();
			dataSource.setDriverClass(getPropValue(fileName, "driverClass"));
			dataSource.setJdbcUrl(getPropValue(fileName, "jdbcUrl"));
			dataSource.setUser(getPropValue(fileName, "user"));
			dataSource.setPassword(getPropValue(fileName, "password"));
			dataSource.setInitialPoolSize(Util.getIntValue(getPropValue(fileName, "minconn")));
			dataSource.setMinPoolSize(Util.getIntValue(getPropValue(fileName,"minconn")));
			dataSource.setMaxPoolSize(Util.getIntValue(getPropValue(fileName,"maxconn")));
			dataSource.setMaxIdleTime(60);
			dataSource.setIdleConnectionTestPeriod(60);
			map.put(propName, dataSource);
		}
		return dataSource;
	}

	public static Connection getConnection() {
		return getConnection(null);
	}
	public static Connection getConnection(String propName) {
		try {
			propName = (propName == null || "".equals(propName)) ? getPropValue("system_def.properties","database"): propName;
			return getDataSource(propName).getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	public static void close(ResultSet rs) {
		try {
			Statement st = rs.getStatement();
			Connection conn=st.getConnection();
			close(rs, st, conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void close(ResultSet rs, Statement st, Connection conn) {
		try {
			if(rs!=null && st==null && conn==null ){
				st=rs.getStatement();
				conn=st.getConnection();
			}
			if (rs != null) {
				rs.close();
			}
			if (st != null) {
				st.close();
			}
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
			rs=null;
			st=null;
			conn=null;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static ResultSet executeQuery(String v_sql) {
		return executeQuery(null, v_sql);
	}

	public static ResultSet executeQuery(String propName, String v_sql) {
		Connection conn = getConnection(propName);
		ResultSet rs = null;
		try {
			PreparedStatement pst = conn.prepareStatement(v_sql);
			rs = pst.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}
	public static String executeQuery(String propName, String v_sql,String v_field_name) {
		String tmp_str = "";
		try {
			Connection conn = getConnection(propName);
			PreparedStatement pst = conn.prepareStatement(v_sql);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				tmp_str = Util.null2String(rs.getString(v_field_name)).trim();
			}
			close(rs, pst, conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return tmp_str;
	}
	public static Map<String, String> executeQueryMap(String propName, String v_sql) {
		ResultSet rs = null;
		Map<String, String> map=new HashMap<String,String>();
		try {
			Connection conn = getConnection(propName);
			PreparedStatement pst = conn.prepareStatement(v_sql);
			rs = pst.executeQuery();
			if(rs.next()==true){
				ResultSetMetaData metaData = rs.getMetaData();
				for(int i=1;i<=metaData.getColumnCount();i++){
					String v_key = Util.null2String(metaData.getColumnName(i));
					String v_val=Util.null2String(rs.getString(v_key));
					map.put(v_key.toLowerCase(), v_val);
				}
			}
			close(rs, pst, conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}
	public static int executeUpdate(String v_sql) {
		return executeUpdate(null, v_sql);
	}

	public static int executeUpdate(String propName, String v_sql) {
		Connection conn = getConnection(propName);
		PreparedStatement pst =null;
		int v_cnt = 0;
		try {
			pst = conn.prepareStatement(v_sql);
			v_cnt = pst.executeUpdate();
			close(null, pst, conn);
		} catch (Exception e) {
			close(null, pst, conn);
			e.printStackTrace();
			System.out.println(v_sql);
			v_cnt = -1;
		}
		return v_cnt;
	}

	public static boolean executeBatch(String v_sql, ArrayList<ArrayList<String>> v_objs) {
		return executeBatch(null, v_sql, v_objs);
	}

	public static boolean executeBatch(String propName, String v_sql,ArrayList<ArrayList<String>> v_objs) {
		Connection conn = getConnection(propName);
		boolean v_flag = false;
		try {
			conn.setAutoCommit(false);
			PreparedStatement pst = conn.prepareStatement(v_sql);
			if (v_objs == null) {
				pst.addBatch();
			} else {
				for (ArrayList<String> str_Arr : v_objs) {
					fillArgs(pst, str_Arr);
					pst.addBatch();
				}
			}
			pst.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
			close(null, pst, conn);
			v_flag = true;
		} catch (Exception e) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
				close(null, null, conn);
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return v_flag;
	}

	private static void fillArgs(PreparedStatement pst, ArrayList<String> v_objs)
			throws Exception {
		for (int i = 1; i <= v_objs.size(); i++) {
			String tmp_value = v_objs.get(i-1);
			pst.setString(i,tmp_value);
		}
	}

}
